﻿package pcenshao.commons.simpledb.mssql;

/**
 * MS SQL Server工具类<br>
 * 如,生成分页的SQL
 * 
 * @author pengyw
 * 
 */
public class MsSQLUtil {

	/**
	 * 生成适用SQL Server 2005及以上版本的分页SQL语句<br>
	 * 将按照id顺序分页
	 * <br>
	 * ___row_num
	 * @param tableName
	 *            表名
	 * @param idColumn
	 *            主键列名,最好是自动增长类型
	 * @param where
	 *            条件,不能以where开头,可以为null,或空串
	 * @param offset
	 *            起始行,不能小于0
	 * @param limit
	 *            最大条数,不能小于0
	 * @param selectedCols
	 *            select的所有列,若selectedCols.length=0,则使用*.不能为null
	 * @return
	 */
	public static String generate2005PagingSQL(String tableName,
			String idColumn, String where, long offset, int limit,
			String... selectedCols) {
		if (tableName == null) {
			throw new NullPointerException("tableName=null");
		}
		if (idColumn == null) {
			throw new NullPointerException("idColumn=null");
		}
		if (where == null) {
			where = "";
		}
		if (selectedCols == null) {
			throw new NullPointerException("cols=null");
		}

		StringBuilder buf = new StringBuilder();
		buf.append("with query as ( ");
		buf.append("select ROW_NUMBER() OVER (ORDER BY ").append(idColumn)
				.append(" ) as ___row_num,");
		buf.append(" ");
		if (selectedCols.length == 0) {
			buf.append(" * ");
		} else {
			for (String col : selectedCols) {
				buf.append(col);
				buf.append(",");
			}
			buf.deleteCharAt(buf.length() - 1); // 删除最后的,
		}
		buf.append(" from ");
		buf.append(tableName);
		buf.append(" ");
		if (where != null && where.trim().length() > 0) { // where不为空的情况下添加
			buf.append(" where ");
			buf.append(where);
		}
		buf.append(" ) "); // with query as () 的括号
		buf.append("select * from query where ___row_num between "); 
		buf.append(offset + 1);
		buf.append(" and "); 
		buf.append(offset + limit);
		buf.append(" ");
		return buf.toString();
	}
	
	public static void main(String[] args) {
		System.out.println(MsSQLUtil.generate2005PagingSQL("tb_data_guangdonginval", "id", 
				"(select count(1) from tb_bizinfo_guangdong_snaphtml b where tb_data_guangdonginval.companyName = b.companyName and tb_data_guangdonginval.site = b.site ) = 0", 0, 10, new String[]{
				"id","companyName","site","urlStatus","catagory"
		}));
	}
}
